package banco;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import utilidades.FormataNumero;
import utilidades.FormatarData;
import dominio.TJLP;
import dominio.Tipo_Planilha;
import dominio.Valores;
import fachada.FachadaTJLP;

public class TjlpDAO implements FachadaTJLP {

public void insertTJLP(TJLP t, Tipo_Planilha tp, Valores v) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = Banco.getConexaoEXTRATOPAES();
            stmt = conn.prepareStatement("insert into tjlp (tjlp, pagamento, mes, id_valores, id_tipo_planilha) values (?, ?, ?, ?, ?)");
            stmt.setDouble(1, t.getTjlp());
            stmt.setDouble(2, t.getPagamento());
            stmt.setString(3, FormatarData.formatar(t.getMes()));
            stmt.setInt(4, v.getId());
            stmt.setInt(5, tp.getId());
            stmt.executeUpdate();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
        
        catch (Exception e) {
        	e.printStackTrace();
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
        }
    }

@SuppressWarnings("finally")
public Collection selectTJLP(Valores v, Tipo_Planilha tp) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    Collection<TJLP> res = new ArrayList<TJLP>();
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("select * from tjlp where id_valores = ? and id_tipo_planilha = ?");
        stmt.setInt(1, v.getId());
        stmt.setInt(2, tp.getId());
        rs = stmt.executeQuery();
        while (rs.next()){ 
        	TJLP t = new TJLP();
        	t.setMes(FormatarData.inserirData(rs.getString("mes")));
        	t.setPagamento(FormataNumero.formatarSaida(rs.getFloat("pagamento")));
        	t.setTjlp(FormataNumero.formatarSaida(rs.getFloat("tjlp")));
        	t.setData_string(FormatarData.getMesAno(rs.getString("mes")));
        	res.add(t);
        }
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;
    }
}

public void updateTJLP(TJLP t, Tipo_Planilha tp, Valores v) throws ClassNotFoundException, IOException {
    
    Connection conn = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    
    try {
        conn = Banco.getConexaoEXTRATOPAES();
        stmt = conn.prepareStatement("update tjlp set pagamento = ? where id_valores = ? and id_tipo_planilha = ? and mes = ?");
        stmt.setFloat(1, t.getPagamento());
        stmt.setInt(2, v.getId());
        stmt.setInt(3, tp.getId());
        stmt.setString(4, FormatarData.formatar(t.getMes()));
        stmt.executeUpdate();
    }
    catch (SQLException ex) {
        ex.printStackTrace();
    }
    
    catch (Exception e) {
    	e.printStackTrace();
    }
    finally {
        Banco.closeConexao(conn, rs, stmt);
    }
}
	
}
